#!/bin/bash

APP=gmall

# 判断日期参数，日期参数是第二个参数
if [ -n "$2" ]; then
    do_date=$2
else
    echo "请传入日期参数"
    exit
fi

common_sql="use ${APP};set hive.exec.dynamic.partition.mode=nonstrict;"

dws_trade_user_sku_order_1d_sql="
    insert overwrite table dws_trade_user_sku_order_1d partition (dt)
    select
        user_id,
        sku_id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name,
        order_count_1d,
        order_num_1d,
        order_original_amount_1d,
        activity_reduce_amount_1d,
        coupon_reduce_amount_1d,
        order_total_amount_1d,
        t1.dt
    from (
        select
            user_id,
            sku_id,
            count(*) order_count_1d,
            sum(sku_num) order_num_1d,
            sum(if (split_original_amount is null, 0, split_original_amount)) order_original_amount_1d,
            sum(if (split_total_amount is null, 0, split_total_amount)) order_total_amount_1d,
            sum(if (split_activity_amount is null, 0, split_activity_amount)) activity_reduce_amount_1d,
            sum(if (split_coupon_amount is null, 0, split_coupon_amount)) coupon_reduce_amount_1d,
            dt
        from dwd_trade_order_detail_inc
        where dt <= '${do_date}'
        group by dt, user_id, sku_id
    ) t1
    left join (
        select
            id,
            sku_name,
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name,
            tm_id,
            tm_name
        from dim_sku_full
        where dt = '${do_date}'
    ) t2 on t1.sku_id = t2.id;
"

dws_trade_user_order_1d_sql="
    insert overwrite table dws_trade_user_order_1d partition (dt)
    select
        user_id,
        count(distinct order_id) order_count_1d,
        sum(sku_num) order_num_1d,
        sum(if (split_original_amount is null, 0, split_original_amount)) order_original_amount_1d,
        sum(if (split_total_amount is null, 0, split_total_amount)) order_total_amount_1d,
        sum(if (split_activity_amount is null, 0, split_activity_amount)) activity_reduce_amount_1d,
        sum(if (split_coupon_amount is null, 0, split_coupon_amount)) coupon_reduce_amount_1d,
        dt
    from dwd_trade_order_detail_inc
    where dt <= '${do_date}'
    group by dt, user_id;
"

dws_trade_user_cart_add_1d_sql="
    insert overwrite table dws_trade_user_cart_add_1d partition (dt)
    select
        user_id,
        count(*) cart_add_count_1d,
        sum(sku_num) cart_add_num_1d,
        dt
    from dwd_trade_cart_add_inc
    where dt <= '${do_date}'
    group by dt, user_id;
"

dws_trade_user_payment_1d_sql="
    insert overwrite table dws_trade_user_payment_1d partition (dt)
    select
        user_id,
        count(distinct order_id) payment_count_1d,
        sum(sku_num) payment_num_1d,
        sum(split_payment_amount) payment_amount_1d,
        dt
    from dwd_trade_pay_detail_suc_inc
    where dt <= '${do_date}'
    group by user_id, dt;
"

dws_trade_province_order_1d_sql="
    with order_info as (
        select
            province_id,
            count(distinct order_id) order_count_1d,
            sum(sku_num) order_num_1d,
            sum(if (split_original_amount is null, 0, split_original_amount)) order_original_amount_1d,
            sum(if (split_total_amount is null, 0, split_total_amount)) order_total_amount_1d,
            sum(if (split_activity_amount is null, 0, split_activity_amount)) activity_reduce_amount_1d,
            sum(if (split_coupon_amount is null, 0, split_coupon_amount)) coupon_reduce_amount_1d,
            dt
        from dwd_trade_order_detail_inc
        where dt <= '${do_date}'
        group by dt, province_id
    ), province_info as (
        select
            id, province_name, area_code, iso_code, iso_3166_2
        from dim_province_full
        where dt = '${do_date}'
    )
    insert overwrite table dws_trade_province_order_1d partition (dt)
    select
        province_id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2,
        order_count_1d,
        order_num_1d,
        order_original_amount_1d,
        activity_reduce_amount_1d,
        coupon_reduce_amount_1d,
        order_total_amount_1d,
        dt
    from order_info
    left join province_info on order_info.province_id = province_info.id;
"

dws_tool_user_coupon_coupon_used_1d_sql="
    with use_coupon as (
        select
            user_id,
            coupon_id,
            count(*) used_count_1d,
            dt
        from dwd_tool_coupon_used_inc
        where dt <= '${do_date}'
        group by dt, coupon_id, user_id
    ), coupon_info as (
        select
            id, coupon_name, coupon_type_code, coupon_type_name, benefit_rule
        from dim_coupon_full
        where dt = '${do_date}'
    )
    insert overwrite table dws_tool_user_coupon_coupon_used_1d partition (dt)
    select
        coupon_id,
        user_id,
        coupon_name,
        coupon_type_code,
        coupon_type_name,
        benefit_rule,
        used_count_1d,
        dt
    from use_coupon
    left join coupon_info on use_coupon.coupon_id = coupon_info.id;
"

dws_interaction_sku_favor_add_1d_sql="
    with favor_info as (
        select
            sku_id,
            count(*) favor_add_count_1d,
            dt
        from dwd_interaction_favor_add_inc
        where dt <= '${do_date}'
        group by sku_id, dt
    ), sku_info as (
        select
            id,
            sku_name,
            tm_id,
            tm_name,
            category3_id,
            category3_name,
            category2_id,
            category2_name,
            category1_id,
            category1_name
        from dim_sku_full
        where dt = '${do_date}'
    )
    insert overwrite table dws_interaction_sku_favor_add_1d partition (dt)
    select
        sku_id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name,
        favor_add_count_1d,
        dt
    from favor_info
    left join sku_info on sku_info.id = favor_info.sku_id;
"

dws_traffic_session_page_view_1d_sql="
    insert overwrite table dws_traffic_session_page_view_1d partition (dt = '${do_date}')
    select
        session_id,
        mid_id,
        brand,
        model,
        operate_system,
        version_code,
        channel,
        sum(during_time) during_time_1d,
        count(*) page_count_1d
    from dwd_traffic_page_view_inc
    where dt = '${do_date}'
    group by session_id, mid_id, brand, model, operate_system, version_code, channel;
"

dws_traffic_page_visitor_page_view_1d_sql="
    insert overwrite table dws_traffic_page_visitor_page_view_1d partition (dt = '${do_date}')
    select
        mid_id,
        brand,
        model,
        operate_system,
        page_id,
        sum(during_time) during_time_1d,
        count(*) view_count_1d
    from dwd_traffic_page_view_inc
    where dt = '${do_date}'
    group by mid_id, brand, model, operate_system, page_id;
"

# 判断表名参数，表名参数是第一个参数
case $1 in
"dws_trade_user_sku_order_1d")
    hive -e "${common_sql}${dws_trade_user_sku_order_1d_sql}"
;;
"dws_trade_user_order_1d")
    hive -e "${common_sql}${dws_trade_user_order_1d_sql}"
;;
"dws_trade_user_cart_add_1d")
    hive -e "${common_sql}${dws_trade_user_cart_add_1d_sql}"
;;
"dws_trade_user_payment_1d")
    hive -e "${common_sql}${dws_trade_user_payment_1d_sql}"
;;
"dws_trade_province_order_1d")
    hive -e "${common_sql}${dws_trade_province_order_1d_sql}"
;;
"dws_tool_user_coupon_coupon_used_1d")
    hive -e "${common_sql}${dws_tool_user_coupon_coupon_used_1d_sql}"
;;
"dws_interaction_sku_favor_add_1d")
    hive -e "${common_sql}${dws_interaction_sku_favor_add_1d_sql}"
;;
"dws_traffic_session_page_view_1d")
    hive -e "${common_sql}${dws_traffic_session_page_view_1d_sql}"
;;
"dws_traffic_page_visitor_page_view_1d")
    hive -e "${common_sql}${dws_traffic_page_visitor_page_view_1d_sql}"
;;
"all")
    hive -e "${common_sql}${dws_trade_user_sku_order_1d_sql}${dws_trade_user_order_1d_sql}${dws_trade_user_cart_add_1d_sql}${dws_trade_user_payment_1d_sql}${dws_trade_province_order_1d_sql}${dws_tool_user_coupon_coupon_used_1d_sql}${dws_interaction_sku_favor_add_1d_sql}${dws_traffic_session_page_view_1d_sql}${dws_traffic_page_visitor_page_view_1d_sql}"
;;
esac